package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Account;
import org.csu.mypetstore.domain.SignOn;
import org.csu.mypetstore.persistence.AccountDao;
import org.csu.mypetstore.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public SignOn findUserByUsernameAndPassword(String username, String password) {
        try {
            String sql = "select * from signon where username=? and password=?";
            SignOn user = template.queryForObject(sql,new BeanPropertyRowMapper<SignOn>(SignOn.class),username,password);
            return user;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Account getAccountByUsername(String username) {
        String sql = " SELECT\n" +
                "          SIGNON.USERNAME,\n" +
                "          ACCOUNT.EMAIL,\n" +
                "          ACCOUNT.FIRSTNAME,\n" +
                "          ACCOUNT.LASTNAME,\n" +
                "          ACCOUNT.STATUS,\n" +
                "          ACCOUNT.ADDR1 AS address1,\n" +
                "          ACCOUNT.ADDR2 AS address2,\n" +
                "          ACCOUNT.CITY,\n" +
                "          ACCOUNT.STATE,\n" +
                "          ACCOUNT.ZIP,\n" +
                "          ACCOUNT.COUNTRY,\n" +
                "          ACCOUNT.PHONE,\n" +
                "          PROFILE.LANGPREF AS languagePreference,\n" +
                "          PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
                "          PROFILE.MYLISTOPT AS listOption,\n" +
                "          PROFILE.BANNEROPT AS bannerOption,\n" +
                "          BANNERDATA.BANNERNAME\n" +
                "    FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
                "    WHERE ACCOUNT.USERID = ?\n" +
                "      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
                "      AND PROFILE.USERID = ACCOUNT.USERID\n" +
                "      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
            return template.queryForObject(sql,new BeanPropertyRowMapper<Account>(Account.class),username);
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        String sql = " SELECT\n" +
                "      SIGNON.USERNAME,\n" +
                "      ACCOUNT.EMAIL,\n" +
                "      ACCOUNT.FIRSTNAME,\n" +
                "      ACCOUNT.LASTNAME,\n" +
                "      ACCOUNT.STATUS,\n" +
                "      ACCOUNT.ADDR1 AS address1,\n" +
                "      ACCOUNT.ADDR2 AS address2,\n" +
                "      ACCOUNT.CITY,\n" +
                "      ACCOUNT.STATE,\n" +
                "      ACCOUNT.ZIP,\n" +
                "      ACCOUNT.COUNTRY,\n" +
                "      ACCOUNT.PHONE,\n" +
                "      PROFILE.LANGPREF AS languagePreference,\n" +
                "      PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
                "      PROFILE.MYLISTOPT AS listOption,\n" +
                "      PROFILE.BANNEROPT AS bannerOption,\n" +
                "      BANNERDATA.BANNERNAME\n" +
                "    FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
                "    WHERE ACCOUNT.USERID = ?\n" +
                "      AND SIGNON.PASSWORD = ?\n" +
                "      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
                "      AND PROFILE.USERID = ACCOUNT.USERID\n" +
                "      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Account>(Account.class),account.getUsername(),account.getPassword());
    }

    @Override
    public void insertAccount(Account account) {
        String sql = " INSERT INTO ACCOUNT\n" +
                "      (EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID)\n" +
                "    VALUES\n" +
                "      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        template.update(sql,account.getEmail(),account.getFirstName(),account.getLastName(),account.getStatus(),account.getAddress1(),account.getAddress2(),account.getCity(),account.getState(),account.getZip(),account.getCountry(),account.getPhone(),account.getUsername());
    }

    @Override
    public void insertProfile(Account account) {
        String sql = " INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, MYLISTOPT, BANNEROPT, USERID)\n" +
                "    VALUES (?, ?, ?, ?, ?)";
        template.update(sql,account.getLanguagePreference(),account.getFavouriteCategoryId(),account.getListOption(),account.getBannerOption(),account.getUsername());
    }

    @Override
    public void insertSignon(Account account) {
        String sql = " INSERT INTO SIGNON (PASSWORD,USERNAME)\n" +
                "    VALUES (?,?)";
        template.update(sql,account.getPassword(),account.getUsername());
    }

    @Override
    public void updateAccount(Account account) {
        String sql = " UPDATE ACCOUNT SET\n" +
                "      EMAIL = ?,\n" +
                "      FIRSTNAME = ?,\n" +
                "      LASTNAME = ?,\n" +
                "      STATUS = ?,\n" +
                "      ADDR1 = ?,\n" +
                "      ADDR2 = ?,\n" +
                "      CITY = ?,\n" +
                "      STATE = ?,\n" +
                "      ZIP = ?,\n" +
                "      COUNTRY = ?,\n" +
                "      PHONE = ?\n" +
                "    WHERE USERID = ?";
        template.update(sql,account.getEmail(),account.getFirstName(),account.getLastName(),account.getStatus(),account.getAddress1(),account.getAddress2(),account.getCity(),account.getState(),account.getZip(),account.getCountry(),account.getPhone(),account.getUsername());
    }

    @Override
    public void updateProfile(Account account) {
        String sql = "UPDATE PROFILE SET\n" +
                "      LANGPREF = ?,\n" +
                "      FAVCATEGORY = ?,\n" +
                "      MYLISTOPT = ?,\n" +
                "      BANNEROPT = ?\n" +
                "    WHERE USERID = ?";
        template.update(sql,account.getLanguagePreference(),account.getFavouriteCategoryId(),account.getListOption(),account.getBannerOption(),account.getUsername());
    }

    @Override
    public void updateSignon(Account account) {
        String sql =" UPDATE SIGNON SET PASSWORD = ?\n" +
                "    WHERE USERNAME = ?";
        template.update(sql,account.getPassword(),account.getUsername());
    }

    @Override
    public SignOn is_exit(String username) {
        try {
            String sql = "select * from signon where username=?";
            SignOn user = template.queryForObject(sql,new BeanPropertyRowMapper<SignOn>(SignOn.class),username);
            return user;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
